﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace CVSToExcel
{
    public partial class Form1 : Form
    {
        BackgroundWorker bgFileImport = new BackgroundWorker();
        public Form1()
        {
            InitializeComponent();
            if (Debugger.IsAttached)
            {
                txtRootPath.Text = @"c:\trash\wb";
            }

            CheckIfSql();
        }

        void bgFileImport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //txtStatus.Text = "Conversion complete";
            bgFileImport = null;
        }

        void bgFileImport_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (e.UserState != null && e.UserState.ToString().Contains("Error"))
                MessageBox.Show("Error: " + e.UserState.ToString());
            else if (e.UserState != null)
                txtStatus.Text = e.ProgressPercentage.ToString() + "% " + e.UserState.ToString();
        }

        void bgFileImport_DoWork(object sender, DoWorkEventArgs e)
        {
            ConvertFiles();
        }

        private void btnCreateFile_Click(object sender, EventArgs e)
        {
            bgFileImport = new BackgroundWorker();
            bgFileImport.DoWork += bgFileImport_DoWork;
            bgFileImport.ProgressChanged += bgFileImport_ProgressChanged;
            bgFileImport.RunWorkerCompleted += bgFileImport_RunWorkerCompleted;
            bgFileImport.WorkerReportsProgress = true;

            txtStatus.Text = "Conversion started...";
            bgFileImport.RunWorkerAsync();
        }
        private void ConvertFiles()
        {
            
            if (checkboxToSql.Checked)
                ConvertFilesToSql();
            else
                ConvertFilesToExcel();
        }

        private void ConvertFilesToSql()
        {
            var conStrDialog = new FrmConStr();
            conStrDialog.ShowDialog();

            if (!OkayToProceed()) return;
            FileItem f = null;
            Utils.SqlUtil sqlUtil = new Utils.SqlUtil();
            try
            {
                int totalFiles = lbFiles.Items.Count;
                bgFileImport.ReportProgress(0, "Converting " + totalFiles + "files...");

                for (int i = 0; i < totalFiles; i++)
                {
                    int percent = (i * 100) / totalFiles;
                    f = (FileItem)lbFiles.Items[i];

                    bgFileImport.ReportProgress(percent, "Converting " + f.FileNameOnly);
                    sqlUtil = new Utils.SqlUtil();
                   var result =  sqlUtil.AddToSql(f);

                    bgFileImport.ReportProgress(percent, "File converted successfully " + f.FileNameOnly);
                }
                bgFileImport.ReportProgress(100, "Converstion finished...");
            }
            catch (Exception ex)
            {
                bgFileImport.ReportProgress(0, "Error saving " + f.FileNameOnly + ": " + ex.Message);
                bgFileImport.ReportProgress(100, "Converstion failed...");
            }
        }

        private void ConvertFilesToExcel()
        {
            if (!OkayToProceed()) return;

            ClosedXML.Excel.XLWorkbook wb = null;
            DataSet ds = new DataSet();
            FileItem f = null;

            try
            {
                wb = new ClosedXML.Excel.XLWorkbook(SavePath);
                RemoveTestWorkSheet();

                int totalFiles = lbFiles.Items.Count;
                bgFileImport.ReportProgress(0, "Converting " + totalFiles + "files...");

                for (int i = 0; i < totalFiles; i++)
                {
                    int percent = (i * 100) / totalFiles;
                    f = (FileItem)lbFiles.Items[i];

                    bgFileImport.ReportProgress(percent, "Converting " + f.FileNameOnly);
                    MakeSheet(f, wb);

                    bgFileImport.ReportProgress(percent, "Saving " + f.FileNameOnly);
                    wb.Save();
                }
                bgFileImport.ReportProgress(100, "Converstion finished...");
            }
            catch (Exception ex)
            {
                bgFileImport.ReportProgress(0, "Error saving " + f.FileNameOnly + ": " + ex.Message);
                bgFileImport.ReportProgress(100, "Converstion failed...");
            }
            finally
            {
                if (wb != null)
                {
                    wb.Dispose();
                    wb = null;
                }
            }
        }

        private void MakeSheet(FileItem fileItem, ClosedXML.Excel.XLWorkbook workBook)
        {
            string fileName = fileItem.FullFileName;
            OleDbConnection conn = null;
            DataSet ds = null;
            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " +
                    RootPath + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

                conn.Open();

                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(fileName) + "]", conn);

                ds = new DataSet("Temp");
                adapter.Fill(ds);

                ClosedXML.Excel.IXLWorksheet newWorkSheet = null;
                if (ds.Tables.Count > 0)
                {
                    newWorkSheet = workBook.AddWorksheet(ds.Tables[0], fileItem.TabName);
                }

                if (newWorkSheet != null)
                {
                    foreach (var col in newWorkSheet.Columns())
                    {
                        foreach (var cell in col.Cells())
                        {
                            cell.SetDataType(ClosedXML.Excel.XLCellValues.Text);
                            cell.Value = Regex.Replace(cell.Value.ToString(), @"[^\u0020-\u007E]", string.Empty);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                bgFileImport.ReportProgress(100, "Converstion failed adding file..." + ex.Message);
            }
            finally
            {
                if (conn != null) conn.Close();
                ds = null;
            }
        }

        private void btnPickPath_Click(object sender, EventArgs e)
        {
            var folderDialog = new FolderBrowserDialog();
            folderDialog.Description = "Select path with .csv files";
            folderDialog.ShowNewFolderButton = true;
            folderDialog.ShowDialog();

            if (folderDialog.SelectedPath != "")
            {
                txtRootPath.Text = folderDialog.SelectedPath;
            }
        }

        private void txtRooPath_TextChanged(object sender, EventArgs e)
        {
            BindFileList();
        }

        private void btnRemoveSelected_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            DeleteSelectedItems();
        }

        private void DeleteSelectedItems()
        {
            var selectedList = new List<string>();

            foreach (var item in lbFiles.SelectedItems)
            {
                selectedList.Add(item.ToString());
            }


            foreach (var selectedItem in selectedList)
            {
                for (int n = Files.Count - 1; n >= 0; --n)
                {
                    if (Files[n].ToString().Contains(selectedItem.ToString()))
                    {
                        Files.RemoveAt(n);
                    }
                }
            }
            //foreach (var selectedItem in selectedList)
            //{
            //    for (int n = lbFiles.Items.Count - 1; n >= 0; --n)
            //    {
            //        if (lbFiles.Items[n].ToString().Contains(selectedItem.ToString()))
            //        {
            //            lbFiles.Items.RemoveAt(n);
            //        }
            //    }
            //}
        }

        private void lbFiles_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete)
            {
                DeleteSelectedItems();
                return;
            }

            if (e.KeyCode == Keys.Enter)
            {
                AddTabName(lbFiles.SelectedIndex);
            }

        }

        private void BindFileList()
        {
            _files = null;
            //if (Files == null) return;

            lbFiles.DataSource = Files;
            lbFiles.DisplayMember = "DisplayName";
        }

        private BindingList<FileItem> _files;
        private BindingList<FileItem> Files
        {
            get
            {
                if (_files == null)
                {
                    if (!Directory.Exists(RootPath))
                    {
                        txtStatus.Text = "Root path is not valid.";
                       // MessageBox.Show(string.Format("Select a folder path that exists {0} does not exist.", RootPath), "Enter valid path");
                        _files = null;
                        return null;
                    }

                    DirectoryInfo di = new DirectoryInfo(RootPath);
                    var files = di.GetFiles("*.csv");
                    _files = new System.ComponentModel.BindingList<FileItem>();
                    _files.ListChanged += _files_ListChanged;

                    foreach (var file in files)
                    {
                        _files.Add(new FileItem(file.Name, file.FullName));
                    }
                }
                txtStatus.Text = _files.Count + " files";
                return _files;
            }
        }

        void _files_ListChanged(object sender, ListChangedEventArgs e)
        {
            txtStatus.Text = lbFiles.Items.Count + " files";
        }

        private void lbFiles_DoubleClick(object sender, MouseEventArgs e)
        {

            int index = this.lbFiles.IndexFromPoint(e.Location);
            AddTabName(index);
        }

        private void AddTabName(int indexToChange)
        {
            if (indexToChange != System.Windows.Forms.ListBox.NoMatches)
            {
                FileItem itemToEdit = (FileItem)lbFiles.Items[indexToChange];

                frmRenameFile frmRename = new frmRenameFile(itemToEdit);
                frmRename.ShowDialog();
            }
        }

        private bool OkayToProceed()
        {
            if (txtProjectName.Text == "" && txtRootPath.Text == "")
            {
                MessageBox.Show("Please add a project name and root path", "Invalid fields");
                return false;
            }

            if (lbFiles.Items.Count <= 0)
            {
                MessageBox.Show("No files found to convert.  Please add files to convert.", "Files not found");
                return false;
            }

            if (checkboxToSql.Checked) return true;
            //Only check this when converting to excel.
            if (FileExists)
            {
                var result = MessageBox.Show("The project file exists do you want to overwrite it?", "Overwrite file?", buttons: MessageBoxButtons.YesNo);
                if (result == System.Windows.Forms.DialogResult.Yes)
                {
                    CreateNewFile();
                    return true;
                }
            }
            else
            {
                CreateNewFile();
            }

            return true;
        }

        private void CreateNewFile()
        {
            ClosedXML.Excel.XLWorkbook newBook = new ClosedXML.Excel.XLWorkbook();
            newBook.AddWorksheet("Test");
            newBook.SaveAs(SavePath);
        }

        private void RemoveTestWorkSheet()
        {
             ClosedXML.Excel.XLWorkbook wb = null;
            try
            {
               wb = new ClosedXML.Excel.XLWorkbook(SavePath);
                var testws = wb.Worksheet("Test");
                if (testws != null)
                {
                    testws.Delete();
                }
                wb.Save();
            }
            catch (Exception)
            {

            }
            finally
            {
                if (wb != null)
                {
                    wb.Dispose();
                    wb = null;
                }
            }
        }

        private bool FileExists
        {
            get
            {
                return File.Exists(SavePath);
            }
        }

        private string ProjectName
        {
            get
            {
                if (txtProjectName.Text == "") throw new Exception("Set the project name first!");
                return txtProjectName.Text;
            }
        }

        private string SavePath
        {
            get
            {
                return RootPath + Path.DirectorySeparatorChar + ProjectName + ".xlsx";
            }
        }

        private string RootPath
        {
            get
            {
                if (txtRootPath.Text == "") throw new Exception("Set the root path first!");
                return txtRootPath.Text;
            }
        }

        private bool DirectoryExists
        {
            get
            {
                return Directory.Exists(Path.GetDirectoryName(SavePath));
            }
        }

        private void btnOpenProjectFile_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {

            if (!FileExists)
            {
                MessageBox.Show("The file does not exist.", "File not found");
                return;
            }

            System.Diagnostics.Process.Start(SavePath);
        }

        private void btnOpenProject_Click(object sender, EventArgs e)
        {
            if (!DirectoryExists)
            {
                MessageBox.Show("The directory does not exist.", "Directory not found");
                return;
            }

            System.Diagnostics.Process.Start(Path.GetDirectoryName(SavePath));
        }

        private void checkboxToSql_CheckedChanged(object sender, EventArgs e)
        {
            CheckIfSql();
        }

        private void CheckIfSql()
        {
            if (checkboxToSql.Checked)
            {
                btnCreateFile.Text = "Import...";
                btnOpenProjectFile.Hide();
            }
            else
            {
                btnCreateFile.Text = "Create File";
                btnOpenProjectFile.Show();
            }
        }
    }
}
